



--***************************************************************
--첫번째 테이블  => PDF를 VB.NET에서 XML로 변환후 저장하는 테이블
--***************************************************************
CREATE TABLE [dbo].[YU_2018_XML_TB](
	[DT] [varchar](4) NOT NULL,			-- 2018년도
	[ME_ID] [varchar](10) NOT NULL,		-- 사번
	[YU_NO] [varchar](10) NOT NULL CONSTRAINT [DF_YU_2017_XML_TB_YU_NO]  DEFAULT (''),   --일련번호 ※ 하단 참조
	[DU_XML] [xml] NULL,				--VB.NET에서 변환한 XML 
 CONSTRAINT [PK_YU_2018_XML_TB] PRIMARY KEY CLUSTERED 
(
	[DT] ASC,
	[ME_ID] ASC,
	[YU_NO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  /*  YU_NO  코드   예)  YU01 인경우는 보험료 		
	YU01	☞	보험료
	YU02	☞	의료비
	YU03	☞	교육비
	YU04	☞	직업훈련비
	YU05	☞	교복구입비
	YU06	☞	개인연금저축
	YU07	☞	연금저축
	YU08	☞	퇴직연금
	YU09	☞	신용카드
	YU10	☞	현금영수증
	YU11	☞	직불카드
	YU12	☞	주택차입원리금상환
	YU13	☞	장기저당차입이자
	YU14	☞	주택마련저축
	YU15	☞	전세이자상환
	YU16	☞	소상공인공제부금
	YU17	☞	기부금
	YU18	☞	장기증권저축
	YU19	☞	건강보험
	YU20	☞	국민연금
	YU25	☞	학자금대출
  */
GO


--***************************************************************
--두번째 테이블  => PDF를 VB.NET에서 XML로 변환한 XML파일을 분리하여 저장하는 테이블  
   -- 순번의 맨마지막 SU25 는 해당되는 값만 모아 놓는곳 입니다.
--***************************************************************


CREATE TABLE [dbo].[YU_2018_PDF_TB](
	[DT] [varchar](4) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_DT]  DEFAULT (''),
	[ME_ID] [varchar](10) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_ME_ID]  DEFAULT (''),
	[YU_NO] [varchar](30) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_YU_NO]  DEFAULT (''),
	[SUNBUN] [varchar](10) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SUNBUN]  DEFAULT (''),
	[GU_ID] [varchar](10) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_GU_ID]  DEFAULT (''),
	[SU5] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU5]  DEFAULT (''),
	[SU6] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU6]  DEFAULT (''),
	[SU7] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU7]  DEFAULT (''),
	[SU8] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU8]  DEFAULT (''),
	[SU9] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU9]  DEFAULT (''),
	[SU10] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU10]  DEFAULT (''),
	[SU11] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU11]  DEFAULT (''),
	[SU12] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU12]  DEFAULT (''),
	[SU13] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU13]  DEFAULT (''),
	[SU14] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU14]  DEFAULT (''),
	[SU15] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU15]  DEFAULT (''),
	[SU16] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU16]  DEFAULT (''),
	[SU17] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU17]  DEFAULT (''),
	[SU18] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU18]  DEFAULT (''),
	[SU19] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU19]  DEFAULT (''),
	[SU20] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU20]  DEFAULT (''),
	[SU21] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU21]  DEFAULT (''),
	[SU22] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU22]  DEFAULT (''),
	[SU23] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU23]  DEFAULT (''),
	[SU24] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU24]  DEFAULT (''),
	[SU25] [varchar](100) NOT NULL CONSTRAINT [DF_YU_2018_PDF_TB_SU25]  DEFAULT ('')      --여기는 XML각각 해당되는 값만 최종적으로 모아 놓는곳 
) ON [PRIMARY]

GO